"""
    Adelide - A virtual media bookshelf.
    Copyright (C) Niklas Johansson - jonikl@ituniv.se, Jonas Aronsson - jonaz@zxvf.se,
        Anne-Katrin Krolovitsch - annekatr@ituniv.se

    This program is free software; you can redistribute it and/or
    modify it under the terms of the GNU General Public License
    as published by the Free Software Foundation; either version 2
    of the License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
"""

import sys
sys.path.append('../')
from sqlite3 import dbapi2 as sqlite
import os
from adelib import media

class databaseHandler:
    ## open_connection is used to initiate the database connection.
    #
    # opens a connection to the sqlite database for adelide, used before each query.
    # @param self
    def open_connection(self):
        self.connection = sqlite.connect(self.db_name)
        self.connection.row_factory = sqlite.Row
        self.cursor = self.connection.cursor() 
          
    ## close_connection is used to close the database connection.
    #
    # closes the connection to the sqlite database for adelide, used after each query.
    # @param self
    def close_connection(self):
        self.cursor.close()
        self.connection.close()
    
    ## get_media_types gets all media types from database
    #
    # @return Returns a list of the database tables and rows. These will need to be proccessed.
    # @param self
    def get_media_types(self):
        self.open_connection()
        self.cursor.execute("SELECT * FROM type")
        ret_arr = self.cursor.fetchall()
        self.close_connection()
        return ret_arr

    ## do_search searches the database for the given criteria.
    #
    # @param self
    # @param title string
    # @return returns a list of media objects found. If None is found an empty list is returned (Erlang warning on this one.)
    def do_search(self, title):
        self.open_connection()
        self.cursor.execute("SELECT * FROM media WHERE title LIKE '%" + title + "%'")
        ret_arr = []
        for row in self.cursor:
            n_media = media.Media()
            n_media.media_id = row["id"]
            n_media.title = row["title"]
            n_media.year = row["year"]
            n_media.cover = row["cover"]
            n_media.ean = row["ean"]
            n_media.rating = row["rating"]
            n_media.favorite = row["favorite"]
            n_media.format_id = row["format_id"]
            n_media.type_id = row["type_id"]
            n_media.studio_id = row["studio_id"]
            n_media.publisher_id = row["publisher_id"]
            n_media.tags = [] # Insert fetching of tags here!
            n_media.humans = [] # Insert fetching of human relation here!
            ret_arr.append(n_media)
        self.close_connection()
        return ret_arr

    ## search_media_types searches for media with the given media type id.
    #
    # Used for creating a list of i.e Movies or Books.
    # @param self
    # @param type_id the type id of the given format (1 = Book, 2 = Movie, 3 = Music, 4 = Games)
    # @return returns a list of media corresponding to the type_id given in the argument. If no media is found an empty list is returned. (Erlang warning on this one too.)
    def search_media_types(self, type_id):
        self.open_connection()
        self.cursor.execute("SELECT * FROM media WHERE type_id='" +str(type_id) + "'")
        ret_arr = []
        for row in self.cursor:
            n_media = media.Media()
            n_media.media_id = row["id"]
            n_media.title = row["title"]
            n_media.year = row["year"]
            n_media.cover = row["cover"]
            n_media.ean = row["ean"]
            n_media.rating = row["rating"]
            n_media.favorite = row["favorite"]
            n_media.format_id = row["format_id"]
            n_media.type_id = row["type_id"]
            n_media.studio_id = row["studio_id"]
            n_media.publisher_id = row["publisher_id"]
            n_media.tags = [] # Insert fetching of tags here!
            n_media.humans = [] # Insert fetching of human relation here!
            ret_arr.append(n_media)
        self.close_connection()
        return ret_arr
        
    ## add_to_db adds the given media object to the database. 
    #
    # Adds media object without createing any types of relations to humans or groups.
    # @param self
    # @param media a media object.
    def add_to_db(self, media):
        self.open_connection()
        self.cursor.execute('INSERT INTO media(title, year, cover, ean, rating, favorite, format_id, type_id, studio_id, publisher_id) VALUES(?,?,?,?,?,?,?,?,?,?)',(media.title, media.year, media.cover, media.ean, media.rating, media.favorite, media.format_id, media.type_id, media.studio_id, media.publisher_id))
        self.connection.commit()
        self.close_connection()
    
    ## detele_media removes the media corresponding to the given media id.
    #
    # Deletes the media from the database, does not remove relations in other tables.
    # @param self
    # @param media_id a media id.
    def delete_media(self, media_id):
        self.open_connection()
        self.cursor.execute("DELETE from media where id ='" +str(media_id)+ "'")
        self.connection.commit()
        self.close_connection()
        
    ## execute_query executes the query given as argument. 
    #
    # If this query returns a set of media media will be created. This is only for use in testing.
    # DO NOT USE THIS METHOD FOR ANYTHING OTHER THAN TESTING! It will fail if used for anything else than adding media or retrieving media.
    # @param self
    # @param query to execute
    # @return an array with the data fetched from the supplied query
    def execute_query(self, query):
        self.open_connection()
        self.cursor.execute(query)
        i = 0
        ret_arr = []
        for row in self.cursor:
            n_media = media.Media()
            n_media.media_id = row["id"]
            n_media.title = row["title"]
            n_media.year = row["year"]
            n_media.cover = row["cover"]
            n_media.ean = row["ean"]
            n_media.rating = row["rating"]
            n_media.favorite = row["favorite"]
            n_media.format_id = row["format_id"]
            n_media.type_id = row["type_id"]
            n_media.studio_id = row["studio_id"]
            n_media.publisher_id = row["publisher_id"]
            n_media.tags = [] # Insert fetching of tags here!
            n_media.humans = [] # Insert fetching of human relation here!
            ret_arr.append(n_media)
            i = i + 1
        self.close_connection()
        return ret_arr
        
    ## get_movie_book_information fetches all information if the media is a movie or a book
    #
    # @param self
    # @param media id to fetch
    # @return all data related to the media id
    def get_movie_book_info(self, media_id):
        self.open_connection()
        self.cursor.execute("SELECT media.title, humans.name, type.name, media.year, publisher.name, media.ean, media.rating, media.favorite FROM media, type, humans, media_has_humans, human_types, publisher WHERE media.type_id = type.id AND media.id = media_has_humans.media_id AND media_has_humans.humans_id = humans.id AND media_has_humans.human_type_id = human_types.id AND media.id = '"+ str(media_id) +"' AND media.publisher_id = publisher.id")
        ret_arr = self.cursor.fetchall()
        self.close_connection()
        return ret_arr

    ## get_movie_book_information fetches all information if the media is a music
    #
    # @param self
    # @param media id to fetch
    # @return all data related to the media id  
    def get_music_info(self, media_id):
        self.open_connection()
        self.cursor.execute("SELECT media.title, groups.name, format.name, type.name, media.year, media.publisher_id, media.studio_id, media.ean, media.rating, media.favorite FROM media, type, groups, media_has_group, format WHERE media.type_id = type.id AND media.id = media_has_group.media_id AND media_has_group.group_id = groups.id AND media.format_id = format.id")
        ret_arr = self.cursor.fetchall()
        self.close_connection()
        return ret_arr
    ## create_database initiates a databasefile if there is none
    #        
    # create_database is used for creating an initial database for the user. If a database is present upon start up this method will never be executed.
    # @param self
    def create_database(self):
        # create new db in path db_name
        self.open_connection()
          
        self.cursor.execute("CREATE TABLE format (id INTEGER PRIMARY KEY, description TEXT, name TEXT)")
        self.cursor.execute("INSERT INTO format VALUES(1,'Digital Versitile Disc','DVD')")
        self.cursor.execute("INSERT INTO format VALUES(2,'Video Home System','VHS')")
        self.cursor.execute("INSERT INTO format VALUES(3,'Blue Ray Laser Disc','Blu-ray')")
        self.cursor.execute("INSERT INTO format VALUES(4,'Compact Disc','CD')")
        self.cursor.execute("INSERT INTO format VALUES(5,'Book','Book')")
        self.cursor.execute("CREATE TABLE groups (id INTEGER PRIMARY KEY, year_disbanded INTEGER, year_formed INTEGER, name TEXT)")
        self.cursor.execute("CREATE TABLE human_types (id INTEGER PRIMARY KEY, type TEXT)")
        self.cursor.execute("CREATE TABLE humans (id INTEGER PRIMARY KEY, name TEXT, nationality TEXT, year_of_birth INTEGER, year_of_death INTEGER)")
        self.cursor.execute("CREATE TABLE humans_has_group (id INTEGER PRIMARY KEY, group_id INTEGER, human_id INTEGER)")
        self.cursor.execute("CREATE TABLE media (id INTEGER PRIMARY KEY, format_id INTEGER, type_id INTEGER, title TEXT, year INTEGER, cover TEXT, studio_id INTEGER, publisher_id INTEGER, ean TEXT, rating INTEGER, favorite INTEGER)")
        self.cursor.execute("CREATE TABLE media_has_group (id INTEGER PRIMARY KEY, group_id INTEGER, media_id INTEGER)")
        self.cursor.execute("CREATE TABLE media_has_humans (id INTEGER PRIMARY KEY, human_type_id INTEGER, humans_id INTEGER, media_id INTEGER)")
        self.cursor.execute("CREATE TABLE media_has_tags (id INTEGER PRIMARY KEY, media_id INTEGER, tag_id INTEGER)")
        self.cursor.execute("CREATE TABLE publisher (id INTEGER PRIMARY KEY, name TEXT)")
        self.cursor.execute("CREATE TABLE songs (id INTEGER PRIMARY KEY, disk_nr INTEGER, length NUMERIC, title INTEGER, side TEXT, artist TEXT, media_id TEXT)")
        self.cursor.execute("CREATE TABLE studio (id INTEGER PRIMARY KEY, name TEXT)")
        self.cursor.execute("CREATE TABLE tags (id INTEGER PRIMARY KEY, tag TEXT)")
        self.cursor.execute("CREATE TABLE type (id INTEGER PRIMARY KEY, description TEXT, name TEXT)")
        self.cursor.execute("INSERT INTO type VALUES(1, 'Printed textual media','Book')")
        self.cursor.execute("INSERT INTO type VALUES(2, 'Moving pictures.','Movie')")
        self.cursor.execute("INSERT INTO type VALUES(3, 'Instrumental compositions with or without vocals.','Music')")
        self.cursor.execute("INSERT INTO type VALUES(4, 'Board-, computer-, video-games','Games')")

        self.connection.commit()
        self.close_connection()
        
    ## __init__ is the construct for the database handler
    #
    # Sets db path depending on from where the file is run, creates database file if needed.
    # @param self
    def __init__(self):
        # check path...
        if sys.argv[0] != "./adelide.py":
            self.db_name = '../db/adelide.db'
        else:
            self.db_name = 'db/adelide.db'
          
        # check for db-file
        if not os.access(self.db_name, os.F_OK):
            # create new db-file
            try:
                self.create_database()
            except:
                print 'Could not initiate database.'
